package com.scuec.groupfive.mapper;

import com.scuec.groupfive.entity.FavoriteFolder;
import com.scuec.groupfive.entity.Questions;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Options;
import java.util.List;
import java.util.Map;

@Mapper
public interface FavoriteFolderMapper_z {
    @Select("<script>" +
            "SELECT f.id, f.user_id as userId, f.name, f.description, f.created_at as createdAt, f.updated_at as updatedAt, CAST(COUNT(fav.favorite_id) AS SIGNED) as count " +
            "FROM favorite_folders f " +
            "LEFT JOIN favorites fav ON f.id = fav.folder_id AND fav.user_id = f.user_id AND fav.status = 1 " +
            "<where>" +
            "   <if test='userId != null'>" +
            "       f.user_id = #{userId} " +
            "   </if>" +
            "</where>" +
            "GROUP BY f.id, f.user_id, f.name, f.description, f.created_at, f.updated_at " +
            "ORDER BY f.created_at DESC" +
            "</script>")
    List<FavoriteFolder> findByUserId(@Param("userId") Long userId);

    @Select("SELECT q.question_id as questionId, " +
            "q.subject_id as subjectId, " +
            "q.question_type as questionType, " +
            "q.content, " +
            "q.keyword, " +
            "q.creator_id as creatorId, " +
            "q.status, " +
            "q.type_id as typeId, " +
            "q.chapter_id as chapterId, " +
            "q.original_sequence as originalSequence, " +
            "q.year, " +
            "q.created_at as createdAt, " +
            "q.updated_at as updatedAt, " +
            "cq.options, " +
            "cq.answer as choiceAnswer, " +
            "cq.analysis as choiceAnalysis, " +
            "fbq.blanks, " +
            "fbq.answer_template as answerTemplate, " +
            "fbq.analysis as fillblankAnalysis, " +
            "sq.reference_answer as referenceAnswer, " +
            "sq.answer_hints as answerHints, " +
            "sq.analysis as subjectiveAnalysis, " +
            "f.favorite_id as favoriteId " +
            "FROM questions q " +
            "LEFT JOIN choice_questions cq ON q.question_id = cq.question_id AND q.question_type = 'single_choice' " +
            "LEFT JOIN fill_blank_questions fbq ON q.question_id = fbq.question_id AND q.question_type = 'fill_in_the_blank' " +
            "LEFT JOIN subjective_questions sq ON q.question_id = sq.question_id AND q.question_type = 'subjective' " +
            "LEFT JOIN favorites f ON q.question_id = f.question_id AND f.status = 1 " +
            "WHERE q.question_id = #{id}")
    Questions selectQuestionById(@Param("id") Long id);

    @Select("<script>" +
            "SELECT q.question_id as questionId, " +
            "q.subject_id as subjectId, " +
            "q.question_type as questionType, " +
            "q.content, " +
            "q.keyword, " +
            "q.creator_id as creatorId, " +
            "q.status, " +
            "q.type_id as typeId, " +
            "q.year, " +
            "q.chapter_id as chapterId, " +
            "q.original_sequence as originalSequence, " +
            "q.created_at as createdAt, " +
            "q.updated_at as updatedAt, " +
            "cq.options, " +
            "cq.answer as choiceAnswer, " +
            "cq.analysis as choiceAnalysis, " +
            "fbq.blanks, " +
            "fbq.answer_template as answerTemplate, " +
            "fbq.analysis as fillblankAnalysis, " +
            "sq.reference_answer as referenceAnswer, " +
            "sq.answer_hints as answerHints, " +
            "sq.analysis as subjectiveAnalysis, " +
            "f.favorite_id as favoriteId, " +
            "f.folder_id as folderId " +
            "FROM favorites f " +
            "INNER JOIN questions q ON f.question_id = q.question_id " +
            "LEFT JOIN choice_questions cq ON q.question_id = cq.question_id " +
            "LEFT JOIN fill_blank_questions fbq ON q.question_id = fbq.question_id " +
            "LEFT JOIN subjective_questions sq ON q.question_id = sq.question_id " +
            "WHERE f.user_id = #{userId} " +
            "<if test='folderId != null'>" +
            "   AND f.folder_id = #{folderId} " +
            "</if>" +
            "<if test='examType != null and examType != \"\"'>" +
            "   AND q.type_id = #{examType} " +
            "</if>" +
            "<if test='questionType != null and questionType != \"\"'>" +
            "   AND q.question_type = #{questionType} " +
            "</if>" +
            "<if test='subjectId != null and subjectId != \"\"'>" +
            "   AND q.subject_id = #{subjectId} " +
            "</if>" +
            "ORDER BY f.created_at DESC " +
            "LIMIT #{offset}, #{pageSize}" +
            "</script>")
    List<Questions> selectQuestionsByContent(@Param("userId") Long userId,
                                             @Param("folderId") Long folderId,
                                             @Param("examType") String examType,
                                             @Param("questionType") String questionType,
                                             @Param("subjectId") String subjectId,
                                             @Param("offset") Integer offset,
                                             @Param("pageSize") Integer pageSize);

    @Select("<script>" +
            "SELECT COUNT(*) " +
            "FROM questions q " +
            "INNER JOIN favorites f ON q.question_id = f.question_id " +
            "WHERE f.user_id = #{userId} " +
            "<if test='folderId != null'>" +
            "   AND f.folder_id = #{folderId} " +
            "</if>" +
            "<if test='examType != null and examType != \"\"'>" +
            "   AND q.type_id = #{examType} " +
            "</if>" +
            "<if test='questionType != null and questionType != \"\"'>" +
            "   AND q.question_type = #{questionType} " +
            "</if>" +
            "<if test='subjectId != null and subjectId != \"\"'>" +
            "   AND q.subject_id = #{subjectId} " +
            "</if>" +
            "</script>")
    Integer countQuestions(@Param("userId") Long userId,
                           @Param("folderId") Long folderId,
                           @Param("examType") String examType,
                           @Param("questionType") String questionType,
                           @Param("subjectId") String subjectId);

    @Insert("INSERT INTO favorite_folders (user_id, name, description, created_at, updated_at) " +
            "VALUES (#{userId}, #{name}, #{description}, NOW(), NOW())")
    void insert(FavoriteFolder folder);

    @Update("UPDATE favorite_folders SET name = #{name}, description = #{description}, updated_at = NOW() " +
            "WHERE id = #{id} AND user_id = #{userId}")
    void update(FavoriteFolder folder);

    @Delete("DELETE FROM favorite_folders WHERE id = #{id}")
    void delete(@Param("id") Long id);

    @Update("UPDATE favorites SET folder_id = #{folderId}, updated_at = NOW() " +
            "WHERE user_id = #{userId} AND question_id = #{questionId} AND status = 1")
    void moveQuestion(@Param("userId") Long userId, @Param("questionId") Long questionId, @Param("folderId") Long folderId);

    @Update("UPDATE favorites SET status = 0, updated_at = NOW() " +
            "WHERE user_id = #{userId} AND question_id = #{questionId}")
    void deleteQuestionFromFolder(@Param("userId") Long userId, @Param("questionId") Long questionId);

    @Insert("INSERT INTO favorites (user_id, question_id, folder_id, status, created_at, updated_at) " +
            "VALUES (#{userId}, #{questionId}, #{folderId}, 1, NOW(), NOW()) " +
            "ON DUPLICATE KEY UPDATE folder_id = #{folderId}, status = 1, updated_at = NOW()")
    void addQuestionToFolder(@Param("userId") Long userId, @Param("questionId") Long questionId, @Param("folderId") Long folderId);

    @Select("SELECT * FROM favorite_folders WHERE user_id = #{userId}")
    List<FavoriteFolder> getFoldersByUserId(@Param("userId") Long userId);

    @Insert("INSERT INTO favorite_folders (user_id, name, description, created_at, updated_at) " +
            "VALUES (#{userId}, #{name}, #{description}, NOW(), NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void createFolder(FavoriteFolder folder);

    @Update("UPDATE favorite_folders SET name = #{name}, description = #{description}, updated_at = NOW() " +
            "WHERE id = #{id} AND user_id = #{userId}")
    void updateFolder(FavoriteFolder folder);

    @Delete("DELETE FROM favorite_folders WHERE id = #{id}")
    void deleteFolder(@Param("id") Long id);

    @Select("<script>" +
            "SELECT q.question_id as questionId, " +
            "q.subject_id as subjectId, " +
            "q.question_type as questionType, " +
            "q.content, " +
            "q.keyword, " +
            "q.creator_id as creatorId, " +
            "q.status, " +
            "q.type_id as typeId, " +
            "q.year, " +
            "q.chapter_id as chapterId, " +
            "q.original_sequence as originalSequence, " +
            "q.created_at as createdAt, " +
            "q.updated_at as updatedAt, " +
            "f.favorite_id as favoriteId " +
            "FROM questions q " +
            "INNER JOIN favorites f ON q.question_id = f.question_id " +
            "WHERE f.user_id = #{userId} " +
            "<if test='folderId != null'>" +
            "   AND f.folder_id = #{folderId} " +
            "</if>" +
            "<if test='examType != null and examType != \"\"'>" +
            "   AND q.type_id = (SELECT type_id FROM exam_types WHERE exam_type = #{examType}) " +
            "</if>" +
            "<if test='questionType != null and questionType != \"\"'>" +
            "   AND q.question_type = #{questionType} " +
            "</if>" +
            "<if test='subjectId != null and subjectId != \"\"'>" +
            "   AND q.subject_id = #{subjectId} " +
            "</if>" +
            "ORDER BY f.created_at DESC " +
            "LIMIT #{offset}, #{pageSize}" +
            "</script>")
    List<Questions> getFavoriteQuestions(
            @Param("userId") Long userId,
            @Param("folderId") Long folderId,
            @Param("examType") String examType,
            @Param("questionType") String questionType,
            @Param("subjectId") String subjectId,
            @Param("offset") Integer offset,
            @Param("pageSize") Integer pageSize);

    @Select("<script>" +
            "SELECT COUNT(*) " +
            "FROM questions q " +
            "INNER JOIN favorites f ON q.question_id = f.question_id " +
            "WHERE f.user_id = #{userId} " +
            "<if test='folderId != null'>" +
            "   AND f.folder_id = #{folderId} " +
            "</if>" +
            "<if test='examType != null and examType != \"\"'>" +
            "   AND q.type_id = (SELECT type_id FROM exam_types WHERE exam_type = #{examType}) " +
            "</if>" +
            "<if test='questionType != null and questionType != \"\"'>" +
            "   AND q.question_type = #{questionType} " +
            "</if>" +
            "<if test='subjectId != null and subjectId != \"\"'>" +
            "   AND q.subject_id = #{subjectId} " +
            "</if>" +
            "</script>")
    Integer countFavoriteQuestions(
            @Param("userId") Long userId,
            @Param("folderId") Long folderId,
            @Param("examType") String examType,
            @Param("questionType") String questionType,
            @Param("subjectId") String subjectId);

    @Select("SELECT DISTINCT type_id as value " +
            "FROM questions " +
            "WHERE type_id IS NOT NULL " +
            "ORDER BY type_id")
    List<Map<String, Object>> getDifficultyLevels();
}